package com.rainliang.project.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class Db {
	public static Log logger = LogFactory.getLog(Db.class);
	private static final String DB_POOL_NAME = "connect";

	private static DBConnectionManager connectionManager;
	
	static {
		connectionManager=DBConnectionManager.getInstance();
	}
	
	/**
	 * 插入或者更新操作
	 * 
	 * @param sql
	 * @return
	 */
	public static long executeUpdate(String sql) {
		return executeUpdate(sql, null, true);
	}

	public static long executeUpdate(String sql, Object[] parm) {
		return executeUpdate(sql, parm, true);
	}
	/**
	 * 插入或者更新
	 * @param sql sql语句
	 * @param parm 参数列表
	 * @param ifweb 是否来自于web
	 * @return
	 */
	public static long executeUpdate(String sql, Object[] parm, boolean ifweb) {
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			conn = connectionManager.getConnection(DB_POOL_NAME);
			if (sql == null) {
				return 0;
			}
			boolean ifinsert = false;
			if (sql.indexOf("insert") > -1 || sql.indexOf("INSERT") > -1)
				ifinsert = true;
			if (ifinsert == true) {
				pstmt = conn.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
			} else {
				pstmt = conn.prepareStatement(sql);
			}
			if (parm != null && parm.length > 0) {
				for (int i = 0; i < parm.length; i++) {
					pstmt.setObject(i + 1, parm[i]);
				}
			}
			if (ifinsert) {
				long result = pstmt.executeUpdate();
				ResultSet keys = pstmt.getGeneratedKeys();
				long id = 0;
				if (keys.next()) {
					Long t = keys.getLong(1);
					id = t.intValue();
				}
				if (id != 0) {
					result = id;
				}
				return result;
			} else {
				return pstmt.executeUpdate();
			}
		} catch (Exception ex) {
			logger.error("error", ex);
			return -1;
		} finally {
			closePstmt(pstmt);
			connectionManager.freeConnection(DB_POOL_NAME, conn);
		}
	}

	/**
	 * 批量操作
	 * 
	 * @param sql
	 * @param parmList
	 * @return
	 */
	public static boolean executeBatchUpdate(String sql, List<Object[]> parmList) {
		return executeBatchUpdate(sql, parmList, true);
	}

	public static boolean executeBatchUpdate(String sql,List<Object[]> parmList, boolean ifweb) {
		PreparedStatement pstmt = null;
		Connection conn = null;
		boolean b = false;
		try {
			conn = connectionManager.getConnection(DB_POOL_NAME);
			pstmt = conn.prepareStatement(sql);
			if (parmList != null && parmList.size() > 0) {
				for (int i = 0; i < parmList.size(); i++) {
					Object[] parm = parmList.get(i);
					if (parm != null && parm.length > 0) {
						for (int j = 0; j < parm.length; j++) {
							pstmt.setObject(j + 1, parm[j]);
						}
						pstmt.addBatch();
					}
				}
			}
			int[] num = pstmt.executeBatch();
			if (num != null && num.length > 0) {
				b = true;
			}
		} catch (Exception ex) {
			logger.error("db.executeBatchUpdate error", ex);
		} finally {
			closePstmt(pstmt);
			connectionManager.freeConnection(DB_POOL_NAME, conn);
		}
		return b;
	}

	/**
	 * 查询操作
	 * @param sql
	 * @return
	 */
	public static List<Map<String, Object>> executeQuery(String sql) {
		return executeQuery(sql, null);
	}

	public static List<Map<String, Object>> executeQuery(String sql,
			Object[] parm) {
		return executeQuery(sql, parm, true);
	}

	public static List<Map<String, Object>> executeQuery(String sql,
			Object[] parm, boolean ifweb) {
		
		PreparedStatement pstmt = null;
		ResultSet res = null;
		Connection conn = connectionManager.getConnection(DB_POOL_NAME);
		
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		try {
			long start = new Date(System.currentTimeMillis()).getTime();
			if (sql == null) {
				return null;
			}
			pstmt = conn.prepareStatement(sql);
			if (parm != null && parm.length > 0) {
				for (int i = 0; i < parm.length; i++) {
					if (parm[i] == null) {
						break;
					}
					pstmt.setObject(i + 1, parm[i]);
				}
			}
			res = pstmt.executeQuery();
			long end = new Date(System.currentTimeMillis()).getTime();
			if ((end - start) > 2000) {
				logger.warn(",time is "	+ (end - start) + " long time sql :" + sql);
			}
			if (res != null) {
				int columnCount = res.getMetaData().getColumnCount();
				while (res.next()) {
					Map<String, Object> resultRow = new HashMap<String, Object>();
					for (int i = 1; i <= columnCount; i++) {
						resultRow.put(res.getMetaData().getColumnLabel(i), res
								.getObject(i));
					}
					result.add(resultRow);
					resultRow = null;
				}
			}
			return result;

		} catch (SQLException ex) {
			logger.error("error sql:" + sql, ex);
			return null;
		} finally {
			closeRes(res);
			closePstmt(pstmt);
			connectionManager.freeConnection(DB_POOL_NAME, conn);
		}
	}

	/**
	 * 关闭准备集
	 * @param pstmt
	 */
	public static void closePstmt(PreparedStatement pstmt) {
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (Exception ex) {
				logger.error("close stmt error", ex);
			}
		}
	}

	/**
	 * 关闭结果集
	 * @param res
	 */
	public static void closeRes(ResultSet res) {
		if (res != null) {
			try {
				res.close();
			} catch (Exception ex) {
				logger.error("close res error", ex);
			}
		}
	}
}
